Two main objects: connection and cursor
connection = Connect('databasename', 'username', 'password')connection object methods:
.cursor() - returns a new cursor object using the connection
.commit() - commits any pending transaction to the database
.rollback() - causes the database to roll back to the start of any pending transaction
.close() - closes the database connection
cursor = connection.cursor()
cursor.execute("select * from mytable limit 10")
results = cursor.fetchall()cursor.close()
connection.close()cursor object methods:
.execute() -
.executemany() -
.fetchone() -
.fetchmany() -
.fetchall() -
.nextset() -
.Arraysize() -
.close() -
built-in
dsn = (
"DRIVER={IBM DB2 ODBC DRIVER};"
"DATABASE=BLUDB;"
"HOSTNAME=dashdb-xxx-xxxx-xx-lon02-06.services.eu-gb.bluemix.net;"
"PORT=50000;"
"PROTOCOL=TCPIP;"
"UID=xxx99999;"
"PWD=xxxxxxxxxxxxxxxx;"
)
try:
conn = ibm_db.connect(dsn, "", "")
except:
print ("Unable to connect: ", ibm_db.conn_errormsg() )
server = ibm_db.server_info(conn)
print ("DBMS_NAME: ", server.DBMS_NAME)
print ("DBMS_VER: ", server.DBMS_VER)
print ("DB_NAME: ", server.DB_NAME)
client = ibm_db.client_info(conn)
print ("DRIVER_NAME: ", client.DRIVER_NAME)
print ("DRIVER_VER: ", client.DRIVER_VER)
print ("DATA_SOURCE_NAME: ", client.DATA_SOURCE_NAME)
print ("DRIVER_ODBC_VER: ", client.DRIVER_ODBC_VER)
print ("ODBC_VER: ", client.ODBC_VER)
print ("ODBC_SQL_CONFORMANCE: ", client.ODBC_SQL_CONFORMANCE)
print ("APPL_CODEPAGE: ", client.APPL_CODEPAGE)
print ("CONN_CODEPAGE: ", client.CONN_CODEPAGE)Use the connection object to run queries
stmt = ibm_db.exec_immediate(conn, "select * from table limit 10")
ibm_db.fetch_both(stmt)Use the connection to create another connection object that can be used with pandas
import pandas as pd
import ibm_db_dbi
pconn = ibm_db_dbi.Connection(conn)
df = pd.read_sql("select * from table limit 10", pconn)